# coding: utf-8

import sys
# sys.path.append(r'D:\Ipython\PythonDataMining\Case\VMAXDataMining\VoNR\EasyReportCode')

from EasyReportCode.easy.base.DbHelper import selectTable
import pandas as pd
from EasyReportCode.easy.base.comm import GetDateList,get_pc_info
from EasyReportCode.easy.base.docrender import render

startdate='2022-06-14'
enddate='2022-06-20'
datelists=GetDateList(startdate,enddate)
datelists

pro_name=get_pc_info()
proid=pro_name['province_id']
pronm=pro_name['province']

sql_city="""select city_id, city_name from dim_comm_city  """
df_city=selectTable('SQLITE_VS', sql_city)

# sql_cc="""select concat(causetype,'_',cause) as cc, replace( replace( name,'\\\\\\\\\\\\',''),',',' ') as name from dim_cause  """
# df_cause=select('SQLITE_VS',sql_cc)

# sql_cc="""select concat(causetype,'_',cause) as cc, name from dim_cause  """
# sql_cc="""select causetype || '_' || cause as cc from dim_cause """
sql_cc="""select causetype || '_' || cause as cc, replace( replace( name,'\\\\\\\\\\\\',''),',',' ') as name from dim_cause  """

df_cause=selectTable('SQLITE_VS', sql_cc)

sql_tac="""select tacid,vendor_name, model_name  from dim_terminal  """
df_terminal=selectTable('SQLITE_VS', sql_tac)

df_cause
# sql_flag="""select tacid,vendor_name, model_name  from dim_terminal"""
# df_flag=select('SQLITE_VS',sql_flag)

# # VoNR用户分析
# ## VoNR用户情况
sql="""
select * from aggr_vonr_usercnt_day_%s
where local_city=-1
"""
sql_vonr=sql%(datelists[0].replace('-',''))
df_vonr_user=selectTable("SQLITE_VS", sql_vonr)
for s_date in datelists[1:]:
    sub_sql=sql%(s_date.replace('-',''))
    dfsub =selectTable("SQLITE_VS", sub_sql)
    df_vonr_user= pd.concat([df_vonr_user, dfsub])
df_vonr_pro=df_vonr_user.rename(columns={'clttime':'日期','local_city':'地区','vonr_usercnt':'VoNR用户数'})

sql="""
select clttime, localprovince, sum(call_5gc_users) as call_5gc_users  ,sum(indication_epsfb_users) as indication_epsfb_users
 from aggr_epsfb_city_userscount_day_%s
 where  localcity!=0   and localprovince=%s
 group by clttime, localprovince
"""
sql_vonr=sql%(datelists[0].replace('-',''),proid)
df_epsfb_pro=selectTable("SQLITE_VS", sql_vonr)

for s_date in datelists[1:]:
    sub_sql=sql%(s_date.replace('-',''),proid)
    dfsub =selectTable("SQLITE_VS", sub_sql)
    df_epsfb_pro= pd.concat([df_epsfb_pro, dfsub])


df_epsfb_pro = pd.merge(df_epsfb_pro,df_vonr_pro,left_on=['clttime'],right_on=['日期'],how='left')

df_epsfb_pro=df_epsfb_pro[['clttime','indication_epsfb_users','VoNR用户数']]
df_epsfb_pro=df_epsfb_pro.rename(columns={'clttime':'日期','indication_epsfb_users':'EPSFB用户数'})
df_epsfb_pro=df_epsfb_pro.astype({'EPSFB用户数':'int','日期':str})
df_epsfb_pro=df_epsfb_pro.reset_index(drop=True)
df_epsfb_pro

# 全省一周  EPSFB和VONR 用户情况
pro_eps_user=int(df_epsfb_pro[df_epsfb_pro.日期==datelists[-1].replace("-", "")].EPSFB用户数)
pro_vo_user=int(df_epsfb_pro[df_epsfb_pro.日期==datelists[-1].replace("-", "")].VoNR用户数)

sql="""
select * from aggr_vonr_usercnt_day_%s
where local_city!=-1
"""
sql_vonr=sql%(datelists[0].replace('-',''))
df_vonr_city=selectTable("SQLITE_VS", sql_vonr)
for s_date in datelists[1:]:
    sub_sql=sql%(s_date.replace('-',''))
    dfsub =selectTable("SQLITE_VS", sub_sql)
    df_vonr_city= pd.concat([df_vonr_city, dfsub])
df_vonr_city

df_city['city_id'] = df_city['city_id'].astype(int)

# df_city.info()
df_vonr_city_1 = pd.merge(df_vonr_city,df_city,left_on=['local_city'],right_on=['city_id'],how='left')
df_vonr_city_1=df_vonr_city_1[['clttime','city_name','vonr_usercnt']]

df_vonr_city_1=df_vonr_city_1.rename(columns={'clttime':'日期','city_name':'城市','vonr_usercnt':'VoNR用户数'})

datelists[-1].replace("-", "")
df_vonr_city_1.日期

df_vonr_city_1=df_vonr_city_1[df_vonr_city_1.日期==int(datelists[-1].replace("-", ""))]

df_vonr_city_1=df_vonr_city_1.reset_index(drop=True)
df_vonr_city_1

sql="""
select clttime, localcity, call_5gc_users,indication_epsfb_users
 from aggr_epsfb_city_userscount_day_%s
 where  localcity!=0 and localprovince!=0  
"""
sql_vonr=sql%(datelists[-1].replace('-',''))
sql_vonr

df_epsfb_city=selectTable("SQLITE_VS", sql_vonr)
df_epsfb_city

df_vonr_city_2=df_vonr_city[df_vonr_city.clttime==int(datelists[-1].replace('-',''))]
df_vs_city = pd.merge(df_vonr_city_2,df_epsfb_city,left_on=['local_city'],right_on=['localcity'],how='left')
df_vs_city = pd.merge(df_vs_city,df_city,left_on=['local_city'],right_on=['city_id'],how='left')
df_vs_city=df_vs_city[['clttime_x','city_name','indication_epsfb_users','vonr_usercnt']]
df_vs_city=df_vs_city.rename(columns={'clttime_x':'日期','city_name':'地区','indication_epsfb_users':'EPSFB用户数','vonr_usercnt':'VoNR用户数'})
df_vs_city=df_vs_city.astype({'EPSFB用户数':'int','VoNR用户数':int})
df_vs_city=df_vs_city.sort_values('VoNR用户数',ascending=False)
df_vs_city=df_vs_city.reset_index(drop=True)
df_vs_city

city=list(df_vs_city.地区[0:3])
city_lis=str(city[0])+','+str(city[1])+','+str(city[-1])
city_lis

city_1=str(city[0])
city_1_user=int(df_vs_city[df_vs_city.地区==city_1].VoNR用户数)
city_1_user_ep=int(df_vs_city[df_vs_city.地区==city_1].EPSFB用户数)

city_0=df_vs_city.地区[9]
city_0_user=int(df_vs_city[df_vs_city.地区==city_0].VoNR用户数)
city_0_user_ep=int(df_vs_city[df_vs_city.地区==city_0].EPSFB用户数)
city_0_user
city_0_user_ep
city_0


# ## VoNR终端情况
# ### 终端VoNR用户数

sql="""
select * from aggr_vonr_tac_day_%s

"""
sql_vonr=sql%(datelists[0].replace('-',''))
df_vonr_tac=selectTable("SQLITE_VS", sql_vonr)
for s_date in datelists[1:]:
    sub_sql=sql%(s_date.replace('-',''))
    dfsub =selectTable("SQLITE_VS", sub_sql)
    df_vonr_tac= pd.concat([df_vonr_tac, dfsub])

df_vonr_tac=df_vonr_tac.groupby('tacid').agg({'vonr_usercnt':'mean'})
df_vonr_tac=df_vonr_tac.astype({'vonr_usercnt':'int'})
df_vonr_tac=df_vonr_tac.sort_values('vonr_usercnt',ascending=False)
df_tac_nub = pd.merge(df_vonr_tac,df_terminal,left_on=['tacid'],right_on=['tacid'],how='left')
df_tac_nub.dropna(axis=0,how='any',inplace=True)

df_tacid_ue=df_tac_nub.groupby('model_name',as_index=False).agg({'vonr_usercnt':sum})
df_tacid_ue=df_tacid_ue.rename(columns={'model_name':'终端型号','vonr_usercnt':'用户数'})
df_tacid_ue=df_tacid_ue.sort_values('用户数',ascending=False,na_position='first')
df_tacid_ue=df_tacid_ue.astype({'用户数':int})
vo_ue_number=df_tacid_ue['终端型号'].count()
df_tacid_ue=df_tacid_ue[0:15]
df_tacid_ue=df_tacid_ue.reset_index(drop=True)
df_tacid_ue

ue_lis=list(df_tacid_ue.终端型号)[0:5]
ue_lis=','.join(ue_lis)


# ### 终端VoNR呼叫次数

sql="""
select clttime,tacid,sum(vonr_mo_voice_invites_mw+vonr_mo_video_invites_mw) as  vonr_mo_voice_invites_mw
from aggr_vonr_call_terminal_day_%s
where vonr_mo_voice_invites_mw>1 
group by clttime, tacid
order by vonr_mo_voice_invites_mw desc 
limit 1000
"""
sql_vonr=sql%(datelists[0].replace('-',''))
df_vs=selectTable("SQLITE_VS", sql_vonr)
for s_date in datelists[1:]:
    sub_sql=sql%(s_date.replace('-',''))
    dfsub =selectTable("SQLITE_VS", sub_sql)
    df_vs= pd.concat([df_vs, dfsub])

df=df_vs.groupby('tacid').agg({'vonr_mo_voice_invites_mw':sum})
df=df.sort_values('vonr_mo_voice_invites_mw',ascending=False)
df_tac = pd.merge(df,df_terminal,left_on=['tacid'],right_on=['tacid'],how='left')
df_tac.dropna(axis=0,how='any',inplace=True)
df_tac=df_tac.groupby('model_name',as_index=False).agg({'vonr_mo_voice_invites_mw':sum})
df_tac=df_tac.rename(columns={'model_name':'终端型号','vonr_mo_voice_invites_mw':'VoNR呼叫次数'})
df_tac=df_tac.sort_values('VoNR呼叫次数',ascending=False,na_position='first')
df_tac=df_tac.astype({'VoNR呼叫次数':int})
df_tac=df_tac.reset_index(drop=True)
df_tac =df_tac[0:15]
df_tac
# 终端维度用户 始呼 次数，终端使用情况

ue_lis_0=list(df_tac.终端型号)[0:5]
ue_lis_0=','.join(ue_lis_0)
ue_lis_0


# # VoNR指标分析
# ## VoNR注册指标
# ## VoNR接通指标
# ### 5GC承载建立

sql="""
select clttime,  city_id, 
sum(vonr_request_defaultbeareract_n1n2) as VoNR默认承载建立请求次数    ,                 
 sum(vonr_success_defaultbeareract_n1n2)  as VoNR默认承载建立成功次数 ,
 round(100.0* sum(vonr_success_defaultbeareract_n1n2)/sum(vonr_request_defaultbeareract_n1n2),2) as VoNR默认承载建立成功率,
round(100.0*sum(vonr_delay_defaultbeareract_n1n2)/sum(vonr_success_defaultbeareract_n1n2),2)  as   VoNR默认承载建立时延,                
sum(vonr_request_dedicatedbeareract_n1n2) as VoNR专用承载建立请求次数   ,              
sum(vonr_success_dedicatedbeareract_n1n2)  as VoNR专用承载建立成功次数 ,   
 round(100.0* sum(vonr_success_dedicatedbeareract_n1n2)/sum(vonr_request_dedicatedbeareract_n1n2),2) as VoNR专用承载建立成功率,                          
round(100.0*sum(vonr_delay_dedicatedbeareract_n1n2 )/sum(vonr_success_dedicatedbeareract_n1n2),2)  as   VoNR专用承载建立时延
from aggr_imscore_bearer_day_%s
where city_id!=0 and province_id=%s
group by  clttime,  city_id
"""

sql_vonr=sql%(datelists[0].replace('-',''),proid)
df_vonr_bear=selectTable("SQLITE_VS", sql_vonr)
for s_date in datelists[1:]:
    sub_sql=sql%(s_date.replace('-',''),proid)
    dfsub =selectTable("SQLITE_VS", sub_sql)
    df_vonr_bear= pd.concat([df_vonr_bear, dfsub])

df_vonr_bear_pro= df_vonr_bear.groupby('clttime',as_index=False).agg({'VoNR默认承载建立请求次数':sum,'VoNR默认承载建立成功次数':sum,'VoNR专用承载建立请求次数':sum,'VoNR专用承载建立成功次数':sum})
df_vonr_bear_pro=df_vonr_bear_pro.assign(VoNR专用承载建立成功率=(100*df_vonr_bear_pro.VoNR专用承载建立成功次数/df_vonr_bear_pro.VoNR专用承载建立请求次数) )
df_vonr_bear_pro=df_vonr_bear_pro.assign(VoNR默认承载建立成功率=(100*df_vonr_bear_pro.VoNR默认承载建立成功次数/df_vonr_bear_pro.VoNR默认承载建立请求次数) )
# df_vonr_bear_pro=df_vonr_bear_pro.assign(VoNR专用承载建立时延=(df_vonr_bear_pro.VoNR专用承载建立时延/df_vonr_bear_pro.VoNR专用承载建立成功次数) )
# df_vonr_bear_pro=df_vonr_bear_pro.assign(VoNR默认承载建立时延=(df_vonr_bear_pro.VoNR默认承载建立时延/df_vonr_bear_pro.VoNR默认承载建立成功次数) )
df_vonr_bear_pro=df_vonr_bear_pro.astype({'VoNR专用承载建立成功率':'float'}).round(2)
df_vonr_bear_pro=df_vonr_bear_pro.astype({'VoNR默认承载建立成功率':'float'}).round(2)

df_vonr_bear_pro=df_vonr_bear_pro.rename(columns={'clttime':'日期'})
df_vonr_bear_pro=df_vonr_bear_pro.astype({'日期':str})
df_vonr_bear_pro

# df_vonr_bear_pro.head(5)

df_vonr_bear_ded=round(df_vonr_bear_pro.VoNR专用承载建立成功率.mean(), 2)
df_vonr_bear_def=round(df_vonr_bear_pro.VoNR默认承载建立成功率.mean(), 2)

df_vonr_bear_city=df_vonr_bear[df_vonr_bear.clttime==int(datelists[-1].replace("-",""))]
df_vonr_bear_city = pd.merge(df_vonr_bear_city,df_city,left_on=['city_id'],right_on=['city_id'],how='left')
df_vonr_bear_city=df_vonr_bear_city[['clttime','city_name','VoNR专用承载建立请求次数','VoNR专用承载建立成功次数','VoNR专用承载建立成功率','VoNR专用承载建立时延']]
df_vonr_bear_city=df_vonr_bear_city.rename(columns={'clttime':'日期','city_name':'城市'})
df_vonr_bear_city=df_vonr_bear_city.astype({'日期':str,'VoNR专用承载建立请求次数':int,'VoNR专用承载建立请求次数':int,'VoNR专用承载建立成功率':float,'VoNR专用承载建立时延':float})
df_vonr_bear_city=df_vonr_bear_city.reset_index(drop=True)
df_vonr_bear_city

df_bear_city_poor_1=list(df_vonr_bear_city[df_vonr_bear_city.VoNR专用承载建立成功率 <df_vonr_bear_ded].城市)
df_bear_city_2=df_vonr_bear_city.sort_values('VoNR专用承载建立成功率',ascending=False)
df_bear_city_well_1=list(df_bear_city_2[0:3].城市)
df_bear_city_well_1='、'.join(df_bear_city_well_1)
df_bear_city_poor_1='、'.join(df_bear_city_poor_1)

df_vonr_bear_city_na=df_vonr_bear[df_vonr_bear.clttime==int(datelists[-1].replace("-",""))]
df_vonr_bear_city_na = pd.merge(df_vonr_bear_city_na,df_city,left_on=['city_id'],right_on=['city_id'],how='left')
df_vonr_bear_city_na=df_vonr_bear_city_na[['clttime','city_name','VoNR默认承载建立请求次数','VoNR默认承载建立成功次数','VoNR默认承载建立成功率','VoNR默认承载建立时延']]
df_vonr_bear_city_na=df_vonr_bear_city_na.rename(columns={'clttime':'日期','city_name':'城市'})
df_vonr_bear_city_na=df_vonr_bear_city_na.astype({'日期':str,'VoNR默认承载建立请求次数':int,'VoNR默认承载建立请求次数':int,'VoNR默认承载建立成功率':float,'VoNR默认承载建立时延':float})
df_vonr_bear_city_na=df_vonr_bear_city_na.reset_index(drop=True)
df_vonr_bear_city_na

df_bear_city_poor=list(df_vonr_bear_city_na[df_vonr_bear_city_na.VoNR默认承载建立成功率 <df_vonr_bear_def].城市)
df_bear_city_1=df_vonr_bear_city_na.sort_values('VoNR默认承载建立成功率',ascending=False)
df_bear_city_poor='、'.join(df_bear_city_poor)
df_bear_city_well=list(df_bear_city_1[0:3].城市)
df_bear_city_well='、'.join(df_bear_city_well)
df_bear_city_poor

# ### VoNR呼叫分析
# #### VoNR2VoNR
sql="""
select
clttime,
sum(vonr_mo_voice_invites_v2v_mw) as 'VoNR2VoNR语音始呼请求次数',
sum(vonr_mo_voice_180s_v2v_mw) as 'VoNR2VoNR语音始呼接通次数',
sum(vonr_mo_voice_180s_net_v2v_mw) as 'VoNR2VoNR语音始呼网络接通次数',
round(100.0*sum(vonr_mo_voice_180s_v2v_mw)/sum(vonr_mo_voice_invites_v2v_mw),2) as 'VoNR2VoNR语音始呼接通率',
round(100.0*sum(vonr_mo_voice_180s_net_v2v_mw)/sum(vonr_mo_voice_invites_v2v_mw),2) as 'VoNR2VoNR语音始呼网络接通率',
round(1.0*sum(vonr_mo_voice_180alltime_v2v_mw)/sum(vonr_mo_voice_180s_net_v2v_mw),0) as 'VoNR2VoNR语音始呼接通时延'
from
aggr_imscore_call_day_%s
where city_id!=0 and  province_id=%s
group by   clttime,  province_id

"""

sql_vonr=sql%(datelists[0].replace('-',''),proid)
df_vonr2vonr=selectTable("SQLITE_VS", sql_vonr)
for s_date in datelists[1:]:
    sub_sql=sql%(s_date.replace('-',''),proid)
    dfsub =selectTable("SQLITE_VS", sub_sql)
    df_vonr2vonr= pd.concat([df_vonr2vonr, dfsub])

df_vonr2vonr=df_vonr2vonr.rename(columns={'clttime':'日期'})
df_vonr2vonr=df_vonr2vonr.astype({'日期':str,'VoNR2VoNR语音始呼接通率':float,'VoNR2VoNR语音始呼网络接通率':float,'VoNR2VoNR语音始呼接通时延':int})
df_vonr2vonr=df_vonr2vonr.reset_index(drop=True)
df_vonr2vonr

vov_call_city_rate_net=round(df_vonr2vonr.VoNR2VoNR语音始呼网络接通率.mean(), 2)
vov_call_city_rate=round(df_vonr2vonr.VoNR2VoNR语音始呼接通率.mean(), 2)
vov_call_city_delay=int(df_vonr2vonr.VoNR2VoNR语音始呼接通时延.mean())

sql="""
select
clttime,city_id,
sum(vonr_mo_voice_invites_v2v_mw) as 'VoNR2VoNR语音始呼请求次数',
sum(vonr_mo_voice_180s_v2v_mw) as 'VoNR2VoNR语音始呼接通次数',
sum(vonr_mo_voice_180s_net_v2v_mw) as 'VoNR2VoNR语音始呼网络接通次数',
round(100.0*sum(vonr_mo_voice_180s_v2v_mw)/sum(vonr_mo_voice_invites_v2v_mw),2) as 'VoNR2VoNR语音始呼接通率',
round(100.0*sum(vonr_mo_voice_180s_net_v2v_mw)/sum(vonr_mo_voice_invites_v2v_mw),2) as 'VoNR2VoNR语音始呼网络接通率',
round(1.0*sum(vonr_mo_voice_180alltime_v2v_mw)/sum(vonr_mo_voice_180s_net_v2v_mw),0) as 'VoNR2VoNR语音始呼接通时延'
from
aggr_imscore_call_day_%s
where city_id!=0  and province_id=%s
group by   clttime,  city_id
"""

sql_vonr=sql%(datelists[-1].replace('-',''),proid)
df_vonr2vonr_city=selectTable("SQLITE_VS", sql_vonr)

df_vo2vo_city = pd.merge(df_vonr2vonr_city,df_city,left_on=['city_id'],right_on=['city_id'],how='left')
df_vo2vo_city=df_vo2vo_city.rename(columns={'clttime':'日期','city_name':'城市'})
df_vo2vo_city=df_vo2vo_city.astype({'日期':str,'VoNR2VoNR语音始呼接通率':float,'VoNR2VoNR语音始呼网络接通率':float,'VoNR2VoNR语音始呼接通时延':int})
new_name = df_vo2vo_city['城市']
df_vo2vo_city.drop(['城市','city_id'], axis=1,inplace = True)
df_vo2vo_city.insert(1, '城市', new_name)
df_vo2vo_city=df_vo2vo_city.reset_index(drop=True)
df_vo2vo_city

df_vonr2vonr

df_vo2vo_call_avg=float(df_vonr2vonr[df_vonr2vonr.日期==datelists[-1].replace("-", "")].VoNR2VoNR语音始呼网络接通率)
df_vo2vo_call_delay_avg=int(df_vonr2vonr[df_vonr2vonr.日期==datelists[-1].replace("-", "")].VoNR2VoNR语音始呼接通时延)

# ## VoNR切换指标
sql="""
select clttime,  city_id, 
sum(vonr_request_handover_n1n2) as VoNR系统间切换请求次数    ,                 
 sum(vonr_success_handover_n1n2)  as VoNR系统间切换成功次数 ,
 round(100.0* sum(vonr_success_handover_n1n2)/sum(vonr_request_handover_n1n2),2) as VoNR系统间切换成功率
from aggr_imscore_interhandoverout_day_%s
where city_id!=0 and province_id=%s
group by  clttime,  city_id
"""

sql_vonr=sql%(datelists[0].replace('-',''),proid)
df_vonr_ho=selectTable("SQLITE_VS", sql_vonr)
for s_date in datelists[1:]:
    sub_sql=sql%(s_date.replace('-',''),proid)
    dfsub =selectTable("SQLITE_VS", sub_sql)
    df_vonr_ho= pd.concat([df_vonr_ho, dfsub])

df_vonr_ho_pro=df_vonr_ho.groupby('clttime',as_index=False).agg({'VoNR系统间切换请求次数':sum,'VoNR系统间切换成功次数':sum})
df_vonr_ho_pro=df_vonr_ho_pro.assign(VoNR系统间切换成功率=(100*df_vonr_ho_pro.VoNR系统间切换成功次数/df_vonr_ho_pro.VoNR系统间切换请求次数) )
df_vonr_ho_pro=df_vonr_ho_pro.astype({'VoNR系统间切换成功率':'float'}).round(2)
df_vonr_ho_pro=df_vonr_ho_pro.rename(columns={'clttime':'日期'})
df_vonr_ho_pro=df_vonr_ho_pro[['日期','VoNR系统间切换请求次数','VoNR系统间切换成功次数','VoNR系统间切换成功率']]
df_vonr_ho_pro=df_vonr_ho_pro.astype({'日期':str,'VoNR系统间切换请求次数':int,'VoNR系统间切换成功次数':int,'VoNR系统间切换成功率':float})
df_vonr_ho_pro=df_vonr_ho_pro.reset_index(drop=True)
df_vonr_ho_pro

# df_vonr_ho_avg=df_vonr_ho_pro.VoNR系统间切换成功率.mean().round(2)
df_vonr_ho_avg=df_vonr_ho_pro.VoNR系统间切换成功率.mean()
df_vonr_ho_avg

df_vonr_ho_city=df_vonr_ho[df_vonr_ho.clttime==int(datelists[-1].replace("-",""))]
df_vonr_ho_city = pd.merge(df_vonr_ho_city,df_city,left_on=['city_id'],right_on=['city_id'],how='left')
df_vonr_ho_city=df_vonr_ho_city.rename(columns={'clttime':'日期','city_name':'城市'})
df_vonr_ho_city=df_vonr_ho_city[['日期','城市','VoNR系统间切换请求次数','VoNR系统间切换成功次数','VoNR系统间切换成功率']]
df_vonr_ho_city=df_vonr_ho_city.astype({'日期':str,'VoNR系统间切换请求次数':int,'VoNR系统间切换成功次数':int,'VoNR系统间切换成功率':float})
df_vonr_ho_city=df_vonr_ho_city.reset_index(drop=True)
df_vonr_ho_city


# ## VoNR 掉话指标

sql="""
select clttime,  city_id, 
sum(vonr_drop_asr_voice_rx+vonr_drop_asr_video_rx) as VoNR掉话次数,              
 sum(vonr_mo_200_voice_mw+vonr_mt_200_voice_mw+vonr_mo_200_video_mw+vonr_mt_200_video_mw) as VoNR接通次数 ,
 round(100.0* sum(vonr_drop_asr_voice_rx+vonr_drop_asr_video_rx)/sum(vonr_mo_200_voice_mw+vonr_mt_200_voice_mw+vonr_mo_200_video_mw+vonr_mt_200_video_mw),4) as VoNR掉话率
from aggr_imscore_rxasr_day_%s
 where city_id!=0 and province_id=%s
 group by  clttime,  city_id
"""

sql_vonr=sql%(datelists[0].replace('-',''),proid)
df_vonr_asr=selectTable("SQLITE_VS", sql_vonr)
for s_date in datelists[1:]:
    sub_sql=sql%(s_date.replace('-',''),proid)
    dfsub =selectTable("SQLITE_VS", sub_sql)
    df_vonr_asr= pd.concat([df_vonr_asr, dfsub])

df_vonr_asr_pro=df_vonr_asr.groupby('clttime',as_index=False).agg({'VoNR掉话次数':sum,'VoNR接通次数':sum})
df_vonr_asr_pro=df_vonr_asr_pro.assign(VoNR掉话率=(100*df_vonr_asr_pro.VoNR掉话次数/df_vonr_asr_pro.VoNR接通次数) )
df_vonr_asr_pro=df_vonr_asr_pro.astype({'VoNR掉话率':'float'}).round(3)
df_vonr_asr_pro=df_vonr_asr_pro.rename(columns={'clttime':'日期'})
df_vonr_asr_pro=df_vonr_asr_pro.astype({'日期':str,'VoNR掉话次数':int,'VoNR接通次数':int,'VoNR掉话率':float})
df_vonr_asr_pro=df_vonr_asr_pro.reset_index(drop=True)
df_vonr_asr_pro

df_vonr_asr_avg=round(df_vonr_asr_pro.VoNR掉话率.mean(), 3)
df_vonr_asr_avg

# ## VoNR MOS指标

sql="""
select
clttime,
province_id,
sum(vonr_dlrtpsumnum) as VoNR下行RTP总包数,
sum(vonr_ulrtpsumnum) as VoNR上行RTP总包数,
sum(vonr_dlrtplostnum) as VoNR下行RTP丢包数,
sum(vonr_ulrtplostnum) as VoNR上行RTP丢包数,
round(100.0*sum(vonr_dlrtplostnum)/sum(vonr_dlrtpsumnum) ,2)as VoNR下行丢包率,
round(100.0*sum(vonr_ulrtplostnum)/sum(vonr_ulrtpsumnum),2) as VoNR上行丢包率,
round( 1.0*sum(vonr_dlmostotal)/sum(vonr_dlmosnum)/100,2) as VoNR下行平均MOS,
round(1.0*sum(vonr_ulmostotal)/sum(vonr_ulmosnum)/100,2) as  VoNR上行平均MOS
from
aggr_imscore_mos_day_%s

where city_id!=0 or province_id=%s
group by   clttime,  province_id
"""
sql_vonr=sql%(datelists[0].replace('-',''),proid)
df_vonr_mos_pro=selectTable("SQLITE_VS", sql_vonr)
for s_date in datelists[1:]:
    sub_sql=sql%(s_date.replace('-',''),proid)
    dfsub =selectTable("SQLITE_VS", sub_sql)
    df_vonr_mos_pro= pd.concat([df_vonr_mos_pro, dfsub])

# 省级VoNR的MOS指标
df_vonr_mos_pro=df_vonr_mos_pro.rename(columns={'clttime':'日期'})
df_vonr_mos_pro=df_vonr_mos_pro.astype({'日期':str,'VoNR下行丢包率':float,'VoNR上行丢包率':float,'VoNR下行平均MOS':float,'VoNR上行平均MOS':float})
df_vonr_mos_pro.drop(['province_id'],axis=1,inplace=True)
df_vonr_mos_pro=df_vonr_mos_pro.reset_index(drop=True)
df_vonr_mos_pro

vo_mos_rate_dl=round(df_vonr_mos_pro.VoNR下行丢包率.mean(), 2)
vo_mos_rate_ul=round(df_vonr_mos_pro.VoNR上行丢包率.mean(), 2)
vo_mos_ul=round(df_vonr_mos_pro.VoNR上行平均MOS.mean(), 2)
vo_mos_dl=round(df_vonr_mos_pro.VoNR下行平均MOS.mean(), 2)

sql="""
select
clttime,
city_id,
sum(vonr_dlrtpsumnum) as VoNR下行RTP总包数,
sum(vonr_ulrtpsumnum) as VoNR上行RTP总包数,
sum(vonr_dlrtplostnum) as VoNR下行RTP丢包数,
sum(vonr_ulrtplostnum) as VoNR上行RTP丢包数,
round(100.0*sum(vonr_dlrtplostnum)/sum(vonr_dlrtpsumnum) ,2)as VoNR下行丢包率,
round(100.0*sum(vonr_ulrtplostnum)/sum(vonr_ulrtpsumnum),2) as VoNR上行丢包率,
round(1.0*sum(vonr_dlmostotal)/sum(vonr_dlmosnum)/100,2) as VoNR下行平均MOS,
round(1.0*sum(vonr_ulmostotal)/sum(vonr_ulmosnum)/100,2) as  VoNR上行平均MOS
from
aggr_imscore_mos_day_%s
where city_id!=0 and province_id=%s and vonr_dlrtpsumnum!=0  and vonr_ulrtpsumnum!=0
group by   clttime,  city_id
"""
sql_vonr=sql%(datelists[-1].replace('-',''),proid)
df_vonr_mos_city=selectTable("SQLITE_VS", sql_vonr)

df_vonr_mos_city = pd.merge(df_vonr_mos_city,df_city,left_on=['city_id'],right_on=['city_id'],how='left')
df_vonr_mos_city=df_vonr_mos_city.rename(columns={'clttime':'日期','city_name':'城市'})
df_vonr_mos_city=df_vonr_mos_city.astype({'日期':str,'VoNR下行丢包率':float,'VoNR上行丢包率':float,'VoNR下行平均MOS':float,'VoNR上行平均MOS':float})
df_vonr_mos_city.drop(['city_id'],axis=1,inplace=True)
df_vonr_mos_city=df_vonr_mos_city[['日期','城市','VoNR下行丢包率','VoNR上行丢包率','VoNR下行平均MOS','VoNR上行平均MOS']]
df_vonr_mos_city=df_vonr_mos_city.reset_index(drop=True)
df_vonr_mos_city

df_vonr_mos_city_poor_dl='、'.join(list(df_vonr_mos_city[df_vonr_mos_city.VoNR下行平均MOS <vo_mos_dl].城市))
df_vonr_mos_city_well_dl='、'.join(  list(df_vonr_mos_city[df_vonr_mos_city.VoNR下行平均MOS > vo_mos_dl].城市))
df_vonr_mos_city_poor_ul='、'.join( list(df_vonr_mos_city[df_vonr_mos_city.VoNR上行平均MOS <vo_mos_ul].城市))
df_vonr_mos_city_well_ul= '、'.join( list(df_vonr_mos_city[df_vonr_mos_city.VoNR上行平均MOS > vo_mos_ul].城市))
df_vonr_mos_city_well_ul


# # 指标问题分析
# ## 未接通原因分析
# sql="""
#  select province_id, concat(callfirstfail_causetype,'_',callfirstfail_cause) as cc,count(1)fail_nub
#  from aggr_imscore_callfirstfail_multi_day_%s
#  where province_id=%s  and callfirstfail_call_side=1 and (vonr_fail_mo_voice_180s_mw!=0 or vonr_fail_mo_video_180s_mw!=0   )
#  group by province_id, concat(callfirstfail_causetype,'_',callfirstfail_cause)
# """
sql="""
 select province_id, callfirstfail_causetype || '_' || callfirstfail_cause as cc,count(1) as fail_nub
 from aggr_imscore_callfirstfail_multi_day_%s
 where province_id=%s  and callfirstfail_call_side=1 and (vonr_fail_mo_voice_180s_mw!=0 or vonr_fail_mo_video_180s_mw!=0   )
 group by province_id, callfirstfail_causetype || '_' || callfirstfail_cause
"""

sql_vonr=sql%(datelists[0].replace('-',''),proid)
df_vonr_call_cc_net=selectTable("SQLITE_VS", sql_vonr)
for s_date in datelists[1:]:
    sub_sql=sql%(s_date.replace('-',''),proid)
    dfsub =selectTable("SQLITE_VS", sub_sql)
    df_vonr_call_cc_net= pd.concat([df_vonr_call_cc_net, dfsub])

# cc
df_vonr_call_cc_net=df_vonr_call_cc_net.groupby('cc',as_index=False).agg({'fail_nub':sum})
df_vonr_call_cc_net= pd.merge(df_vonr_call_cc_net,df_cause,left_on=['cc'],right_on=['cc'],how='left')
df_vonr_call_cc_net=df_vonr_call_cc_net.sort_values('fail_nub',ascending=False)
df_vonr_call_cc_net=df_vonr_call_cc_net.rename(columns={'fail_nub':'失败次数','name':'失败原因'})
df_vonr_call_cc_net=df_vonr_call_cc_net[['失败原因','失败次数']]
df_vonr_call_cc_net=df_vonr_call_cc_net.astype({'失败次数':int})
df_vonr_call_cc_net=df_vonr_call_cc_net.reset_index(drop=True)
df_vonr_call_cc_net_1=df_vonr_call_cc_net
df_vonr_call_cc_net=df_vonr_call_cc_net[0:20]
df_vonr_call_cc_net

#VoNR未接通  原因总览
sql="""
 select province_id, callfirstfail_causetype || '_' || callfirstfail_cause as cc,count(1) as fail_nub
 from aggr_imscore_callfirstfail_multi_day_%s
 where province_id!=0  and callfirstfail_call_side=1 and (vonr_fail_mo_invite_voice_mw_net!=0 or vonr_fail_mo_invite_video_mw_net!=0   )
 group by province_id, callfirstfail_causetype || '_' || callfirstfail_cause
"""
sql_vonr=sql%(datelists[0].replace('-',''))
df_vonr_call_cc=selectTable("SQLITE_VS", sql_vonr)
for s_date in datelists[1:]:
    sub_sql=sql%(s_date.replace('-',''))
    dfsub =selectTable("SQLITE_VS", sub_sql)
    df_vonr_call_cc= pd.concat([df_vonr_call_cc, dfsub])

df_vonr_call_cc=df_vonr_call_cc.groupby('cc',as_index=False).agg({'fail_nub':sum})
df_vonr_call_cc= pd.merge(df_vonr_call_cc,df_cause,left_on=['cc'],right_on=['cc'],how='left')
df_vonr_call_cc=df_vonr_call_cc.sort_values('fail_nub',ascending=False)
df_vonr_call_cc=df_vonr_call_cc.rename(columns={'fail_nub':'失败次数','name':'失败原因'})
df_vonr_call_cc=df_vonr_call_cc[['失败原因','失败次数']]
df_vonr_call_cc=df_vonr_call_cc.astype({'失败次数':int})
df_vonr_call_cc=df_vonr_call_cc.reset_index(drop=True)
df_vonr_call_cc_top=df_vonr_call_cc[0:10]
df_vonr_call_cc_top

# 剔除用户原因的VoNR未接通
cc_lis=list(df_vonr_call_cc_top.失败原因)[0]+' ,'
k=0
for i in list(df_vonr_call_cc_top.失败原因)[1:5]:
    k+=1
    if k<4:
        cc_lis+=i+','
    else:
        cc_lis+=i
cc_lis=cc_lis.replace(' ','_')
cc_lis

# ## 承载建立失败分析
sql="""
select province_id, causetype || '_' || cause as cc, count( 1) as fail_nub
from aggr_imscore_bearerdelimit_day_%s
where  vonr_fail_dedicatedbeareract_n1n2!=0 and province_id=290
group by clttime, province_id, causetype || '_' || cause
"""
# sql_vonr=sql%(datelists[0].replace('-',''),proid)
sql_vonr=sql%(datelists[0].replace('-',''))
df_vonr_bear_cc=selectTable("SQLITE_VS", sql_vonr)
for s_date in datelists[1:]:
    sub_sql=sql%(s_date.replace('-',''))
    dfsub =selectTable("SQLITE_VS", sub_sql)
    df_vonr_bear_cc= pd.concat([df_vonr_bear_cc, dfsub])

df_vonr_bear_cc=df_vonr_bear_cc.groupby('cc',as_index=False).agg({'fail_nub':sum})
df_vonr_bear_cc= pd.merge(df_vonr_bear_cc,df_cause,left_on=['cc'],right_on=['cc'],how='left')
df_vonr_bear_cc=df_vonr_bear_cc.sort_values('fail_nub',ascending=False)
df_vonr_bear_cc=df_vonr_bear_cc.rename(columns={'fail_nub':'失败次数','name':'失败原因'})
df_vonr_bear_cc=df_vonr_bear_cc[['失败原因','失败次数']]
df_vonr_bear_cc=df_vonr_bear_cc.astype({'失败次数':int})
df_vonr_bear_cc=df_vonr_bear_cc.reset_index(drop=True)
df_vonr_bear_cc=df_vonr_bear_cc[df_vonr_bear_cc.失败原因 != '--']
df_vonr_bear_cc_top=df_vonr_bear_cc[0:10]
df_vonr_bear_cc

# #VoNR专载建立失败主要原因
# cc_bear_lis=list(df_vonr_bear_cc.失败原因)[0]+' ,'
# k=0
# for i in list(df_vonr_bear_cc.失败原因)[1:5]:
#     k+=1
#     if k<4:
#         cc_bear_lis+=i+','
#     else:
#         cc_bear_lis+=i
# cc_bear_lis=cc_bear_lis.replace(' ','_')
# cc_bear_lis

cc_bear_lis=','.join(list(df_vonr_bear_cc.失败原因)[0:3])
cc_bear_lis

sql="""
select province_id, causetype || '_' || cause as cc,count(1) as fail_nub
from aggr_imscore_bearerdelimit_day_%s
where  vonr_fail_defaultbeareract_n1n2!=0 and province_id!=0
group by clttime, province_id, causetype || '_' || cause  
"""
sql_vonr=sql%(datelists[0].replace('-',''))
df_vonr_bear_defalut_cc=selectTable("SQLITE_VS", sql_vonr)
for s_date in datelists[1:]:
    sub_sql=sql%(s_date.replace('-',''))
    dfsub =selectTable("SQLITE_VS", sub_sql)
    df_vonr_bear_defalut_cc= pd.concat([df_vonr_bear_defalut_cc, dfsub])

df_vonr_bear_defalut_cc=df_vonr_bear_defalut_cc.groupby('cc',as_index=False).agg({'fail_nub':sum})
df_vonr_bear_defalut_cc= pd.merge(df_vonr_bear_defalut_cc,df_cause,left_on=['cc'],right_on=['cc'],how='left')
df_vonr_bear_defalut_cc=df_vonr_bear_defalut_cc.sort_values('fail_nub',ascending=False)
df_vonr_bear_defalut_cc=df_vonr_bear_defalut_cc.rename(columns={'fail_nub':'失败次数','name':'失败原因'})
df_vonr_bear_defalut_cc=df_vonr_bear_defalut_cc[['失败原因','失败次数']]
df_vonr_bear_defalut_cc=df_vonr_bear_defalut_cc.astype({'失败次数':int})
df_vonr_bear_defalut_cc=df_vonr_bear_defalut_cc.reset_index(drop=True)
df_vonr_bear_defalut_cc_top=df_vonr_bear_defalut_cc[0:10]

#VoNR载建立失败主要原因
cc_bear_def_lis=','.join(list(df_vonr_bear_defalut_cc_top.失败原因)[0:5])
cc_bear_def_lis

result='XX移动VoNR分析周报%s_%s.docx' %(datelists[0].replace('-',''),datelists[-1].replace('-',''))

print(result)
render(r'D:\Ipython\PythonDataMining\Case\VMAXDataMining\VoNR\EasyReportCode\Report\VoNR\VoNR_Report_Template.docx', result)
print('end'*20)